import 'package:sqlite3/sqlite3.dart';
import '../model/model_user_album.dart';
import '../tools/tools_database.dart';

class DaoUserAlbum {
  static int insert(ModelUserAlbum m) {
    // 1. 连接数据库
    Database db = ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
    INSERT INTO 
      p001_user_album(
        name,file_path,type,language_id,
        media_qty,finish_qty,word_qty,sentence_qty,
        speed,level,last_study_time,
        gmt_create,gmt_modified,remark)
    VALUES
      (
      ?,?,?,?,
      ?,?,?,?,
      ?,?,?,
      ?,?,?
      )
    ;
    ''';
    final stmt = db.prepare(sql);
    DateTime now = DateTime.now();
    int gmt_create = now.millisecondsSinceEpoch;
    try {
      stmt
        ..execute([
          m.name,
          m.filePath,
          m.type ?? "",
          m.languageId ?? 0,
          m.mediaQty ?? 0,
          m.finishQty ?? 0,
          m.wordQty ?? 0,
          m.sentenceQty ?? 0,
          m.speed ?? 0,
          m.level ?? 0,
          m.lastStudyTime ?? 0,
          gmt_create,
          gmt_create,
          m.remark ?? ""
        ]);
    } catch (e) {
      print(e);
    }

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static int deleteById(int id) {
    // 1. 连接数据库
    Database db = ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      delete from 
        p001_user_album 
      where 
        id = ?;
    ''';
    final stmt = db.prepare(sql);
    stmt..execute([id]);

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  // 待修改
  static int updateById(ModelUserAlbum m) {
    print(m);
    // 1. 连接数据库
    Database db = ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      update 
        p001_user_album
      set
       name=?,file_path=?,type=?,language_id=?,
        media_qty=?,finish_qty=?,word_qty=?,sentence_qty=?,
        speed=?,level=?,last_study_time=?,
        gmt_modified = ?,remark = ?
      where
        id = ?
      ;
    ''';

    final stmt = db.prepare(sql);
    try {
      int gmt_modified = DateTime.now().millisecondsSinceEpoch;
      stmt
        ..execute([
          m.name,
          m.filePath,
          m.type,
          m.languageId,
          m.mediaQty,
          m.finishQty,
          m.wordQty,
          m.sentenceQty,
          m.speed,
          m.level,
          m.lastStudyTime,
          gmt_modified,
          m.remark,
          m.id
        ]);
      print("执行成功");
    } catch (e) {
      print("出现异常");
      print(e);
    }

    // 3. 关闭连接
    db.dispose();
    return 0;
  }

  static List<ModelUserAlbum> findAll() {
    // 1. 连接数据库
    Database db = ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_user_album
      ;
    ''';
    ResultSet rs = db.select(sql);
    Iterator<Row> i = rs.iterator;
    List<ModelUserAlbum> list = [];
    while (i.moveNext()) {
      Row r = i.current;
      print(r);

      ModelUserAlbum w = new ModelUserAlbum();
      w.id = r["id"];
      w.name = r["name"];
      w.filePath = r["file_path"];
      w.type = r["type"];
      w.languageId = r["language_id"];
      w.mediaQty = r["media_qty"];
      w.finishQty = r["finish_qty"];
      w.wordQty = r["word_qty"];
      w.sentenceQty = r["sentence_qty"];
      w.speed = r["speed"];
      w.level = r["level"];

      w.gmtCreate = r["gmt_create"];
      w.gmtModified = r["gmt_modified"];
      w.remark = r["remark"];

      list.add(w);
    }

    // 3. 关闭连接
    db.dispose();
    return list;
  }

  static ModelUserAlbum? findById(int id) {
    ModelUserAlbum? m = null;

    // 1. 连接数据库
    Database db = ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_user_album
      where
        id = ?
      ;
    ''';
    ModelUserAlbum? w = null;
    ResultSet rs = db.select(sql, [id]);
    Iterator<Row> i = rs.iterator;
    if (i.moveNext()) {
      Row r = i.current;
      print(r);

      w = new ModelUserAlbum();
      w.id = r["id"];
      w.name = r["name"];
      w.filePath = r["file_path"];
      w.type = r["type"];
      w.languageId = r["language_id"];
      w.mediaQty = r["media_qty"];
      w.finishQty = r["finish_qty"];
      w.wordQty = r["word_qty"];
      w.sentenceQty = r["sentence_qty"];
      w.speed = r["speed"];
      w.level = r["level"];
      w.gmtCreate = r["gmt_create"];
      w.gmtModified = r["gmt_modified"];
      w.remark = r["remark"];
    }

    // 3. 关闭连接
    db.dispose();

    return m;
  }

  static ModelUserAlbum? findByName(String name) {
    // 1. 连接数据库
    Database db = ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_user_album
      where
        name = ?
      ;
    ''';
    ModelUserAlbum? w = null;
    ResultSet rs = db.select(sql, [name]);
    Iterator<Row> i = rs.iterator;
    if (i.moveNext()) {
      Row r = i.current;
      print(r);

      w = new ModelUserAlbum();
      w.id = r["id"];
      w.name = r["name"];
      w.filePath = r["file_path"];
      w.type = r["type"];
      w.languageId = r["language_id"];
      w.mediaQty = r["media_qty"];
      w.finishQty = r["finish_qty"];
      w.wordQty = r["word_qty"];
      w.sentenceQty = r["sentence_qty"];
      w.speed = r["speed"];
      w.level = r["level"];
      w.gmtCreate = r["gmt_create"];
      w.gmtModified = r["gmt_modified"];
      w.remark = r["remark"];
    }

    // 3. 关闭连接
    db.dispose();

    return w;
  }

  static ModelUserAlbum? findByPath(String path) {
    // 1. 连接数据库
    Database db = ToolsDatabase.openDatabase();

    // 2. 执行sql语句
    String sql = '''
      select 
        *
      from
        p001_user_album
      where
        file_path = ?
      ;
    ''';
    ModelUserAlbum? w = null;
    ResultSet rs = db.select(sql, [path]);
    Iterator<Row> i = rs.iterator;
    if (i.moveNext()) {
      Row r = i.current;
      print(r);

      w = new ModelUserAlbum();
      w.id = r["id"];
      w.name = r["name"];
      w.filePath = r["file_path"];
      w.type = r["type"];
      w.languageId = r["language_id"];
      w.mediaQty = r["media_qty"];
      w.finishQty = r["finish_qty"];
      w.wordQty = r["word_qty"];
      w.sentenceQty = r["sentence_qty"];
      w.speed = r["speed"];
      w.level = r["level"];
      w.gmtCreate = r["gmt_create"];
      w.gmtModified = r["gmt_modified"];
      w.remark = r["remark"];
    }

    // 3. 关闭连接
    db.dispose();

    return w;
  }
}
